<?php
/**
 * 《邮箱使用》实现类
 *
 * @author 孙晓晔
 * @version $Id: Zone.class.php 804 2009-12-30 10:05:41Z sunxy $
 */
require_once(dirname(__FILE__) . '/LogUtil.class.php');

class Vlog extends LogUtil {
	public $tbl = Constant::tbl_vpopmail_vlog;

	public $status = array(
		'-' => '请选择',
		'0' => '异常',
		'1' => '帐号不存在',
		'2' => '正常',
		'3' => '密码错误',
		'4' => '访问拒绝',
	);

	public $orderby = array(
		'-' => '请选择',
		'from' => '发送人',
		'to' => '接受人',

		'1' => '创建时间',
		'2' => '更新时间',
	);

	function __construct() {
		$request['update'] = '$Date: 2009/02/16 07:36:07 $';
		$request['revision'] = '$Revision: 1.1 $';

		parent::__construct($request);
		
		$this->dsn = 'vpopmail';
		$this->dsn_r = 'vpopmail';
		$this->dsn_w = 'vpopmail';
	}
	
	/**
	 *
	 *
	 * @param Controller $controller
	 * @param array $request
	 */
	function find($controller, $request) {
		$this->pretreat($controller);
		
		$controller->assign('t1', strtotime('-2 day'));

		$controller->display($request, 'log/vlog/find');
		return true;
	}

	function _orderby_x($controller, $row, $px) {
		$orderby = $this->orderby['1'];
		// $row['orderby_x'] = $row['createtime'];
		$row['orderby_x'] = date("Y-m-d H:i:s", $row['timestamp']);

		if (empty($px) || $px == '-' || $px == '1') {
			$controller->assign('orderby', $orderby);

			return $row;
		}

		if ($px == '2') {
			$orderby = $this->orderby[$px];
			$row['orderby_x'] = $row['modifytime'];
		}

		$controller->assign('orderby', $orderby);

		return $row;
	}

	function _where($request) {
		$user_s = $request['user_s'];
		$error_s = $request['error_s'];
		
		$where = " WHERE 1=1";

		if (!empty($user_s)) {
			$where .= " AND user like '" . $user_s . "%'";
		}
		
		if (!empty($error_s) && $error_s <> '-') {
			$where .= " AND error = '" . $error_s . "'";
		}

		$where .= $this->_where_default($request);
		$where = $this->_where_aa($where);

		return $where;
	}
	
	function _where_default($request) {
		$id_s = $request['id_s'];
		$status_s = $request['status_s'];
		
		$emp_s = $this->_input($request['emp_s']);
		$oper_s = $this->_input($request['oper_s']);
		
		$t1_s = $request['t1_s'];
		$t2_s = $request['t2_s'];
		
		$time_c = $request['time_c_s'];

		$where = '';
		if (!empty($id_s)) {
			// $where .= " AND a.id = '" . $id_s . "'";
			$where .= $this->_id_s($id_s);
		}
		
		if (!empty($status_s) && $status_s <> '-') {
			$where .= " AND a.status = '" . $status_s . "'";
		}
		
		if (!empty($emp_s) && $emp_s <> '-') {
			$where .= " AND emp = '" . $emp_s . "'";
		}
		
		if (!empty($oper_s) && $oper_s <> '-') {
			$where .= " AND oper = '" . $oper_s . "'";
		}
		
		if (empty($time_c) || $time_c == 'create') {
			if (!empty($t1_s)) {
				$where .= " AND DATE_FORMAT(FROM_UNIXTIME(a.timestamp), '%Y-%m-%d %H:%S') > '" . $t1_s . "'";
			}
	
			if (!empty($t2_s)) {
				$where .= " AND DATE_FORMAT(FROM_UNIXTIME(a.timestamp), '%Y-%m-%d  %H:%S') < '" . $t2_s . "'";
			}
		}

		return $where;
	}

	/**
	 *
	 *
	 * @param Controller $controller
	 * @param array $request
	 */
	function findPost($controller, $request) {
		$this->pretreat($controller);

		$this->findPara($controller, $request);

		$db = newdb($this->dsn_w);
		$fmt = "SELECT * FROM %s a %s %s";
		$where = $this->_where($request);
		$orderby = $this->_orderby();
		$sql = sprintf($fmt, $this->tbl, $where, $orderby);
		debug($sql);

		$pager_option = array (
            'db' => $db,
            'sql' => $sql,
            'PageSize' => $this->pagesize,
            'CurrentPageID' => $request['p'],
            'numItems' => $request['n']
		);

		$pager = @new Pager($pager_option);
		$data = $pager->getPageData();

		if ($pager->number > 0) {

			$count = 0;
			$from = $pager->from;
			while ($count < $pager->number) {
				$row = $data[$count];
				$row = $this->_view(null, $row);

				$row = $this->_orderby_x($controller, $row, $this->orderby_s);

				$data[$count] = $row;
				$count++;
			}
			$controller->assign_by_ref('result', $data);
			$controller->assign_by_ref('pager', $pager);
		}

		$controller->display($request, 'log/vlog/list');
		return true;
	}

	/**
	 *
	 *
	 * @param Controller $controller
	 * @param array $request
	 */
	function view($controller, $request) {
		$this->pretreat($controller);

		$id = $request['id'];
		$row = $this->_view($id);

		$controller->assign_by_ref('result', $row);

		$controller->display($request, 'info/message/view');
		return true;
	}
	
	function _view($id = 0, $row = null) {
		if ($row == null) {
			clean($this->tbl . "_id_" . $id);
			$row = $this->id($this->tbl, $id);
		}
		
		if (!empty($row['error'])) {
			$row['error_t'] = $this->status[$row['error']];
		}
				
		$row = $this->_view_t($row);

		return $row;
	}

	function a($controller, $request) {
		
		$ff = "DATE_FORMAT(FROM_UNIXTIME(timestamp), '%Y-%m-%d %H:%S')";
		$dd = date("Y-m-d", strtotime('-7 day'));
		
		$db = newdb($this->dsn_w);
		$fmt = "DELETE FROM %s WHERE %s < '%s'";
		$sql = sprintf($fmt, $this->tbl, $ff, $dd);
		echo $sql . "\n";
		$db->query($sql);

		logdate($controller, $request);
		return true;
	}
	
	function tj33333($controller, $request) {
		$this->pretreat($controller);
	
		$request['tpl'] = "html4";
	
		$from = $this->email;
		$subject = '邮箱使用情况';
	
		$body = $this->_tj_a($controller, $request);
	
		$bb = $this->_list_email('339');
		// print_r($bb);
		if (!$bb || count($bb) == 0) {
			return false;
		}
	
		$this->_tj_mail($from, $bb, $subject, $body);
	
		logdate($controller, $request);
		return true;
	}
	
	/**
	 *
	 *
	 * @param Controller $controller
	 * @param array $request
	 */
	function _tj_a($controller, $request) {
		$this->pretreat($controller);
	
		$groupby_s = 'emp';
		$s = $this->groupby($controller, $groupby_s);
		$df = "DATE_FORMAT(DATE_ADD(now(), INTERVAL -1 DAY), '%Y-%m-%d')";
	
		$db = newdb($this->dsn_r);
		$sql = "SELECT $s AS a, ip, COUNT($s) AS c FROM " . $this->tbl . " a ";
		$where = " WHERE createtime > " . $df;
	
		$orderby = ' ORDER BY c DESC';
	
		$groupby = " GROUP BY $s, ip";
		$sql .= $where . $groupby . $orderby;
		debug($sql);
		$result = $db->query($sql);
	
		$data = array();
		$count = 0;
		$xx = 0;
		$total = 0;
		while ($row = $db->fetch_array($result)) {
			if ($row['a'] == '214') {
				continue;
			}
	
			$total += $row['c'];
			if ($count > 30) {
				$xx += $row['c'];
				continue;
			}
	
			if (empty($row['a'])) {
				continue;
			}
	
			$row = $this->_name($row, $groupby_s);
	
			$row['ip_t'] = $this->name_ip($row['ip']);
	
			$data[] = $row;
			$count++;
		}
	
		if ($xx > 0) {
			$data[] = array('a'=>'其它', 'c'=>$xx);
		}
	
		$aa = array();
		while (list($k, $v) = each($data)) {
			$v['p'] = number_format(($v['c'] / $total) * 100, 2, '.', '') . "%";
			$aa[] = $v;
		}
			
		$a = array();
		$c = array();
		reset($data);
		while (list($k, $v) = each($data)) {
			$a[] = $v['a'];
			$c[] = $v['c'];
		}
			
		$sa = join(",", $a);
		// echo "\n";
		$sc = join(",", $c);
			
		$sa = trim($sa, ",");
			
		$controller->assign('sa', $sa);
		$controller->assign('sc', $sc);
	
		$controller->assign_by_ref('total', $total);
	
		$controller->assign_by_ref('result', $aa);
	
		return $controller->sunxy($request, 'log/action/tj_a');
	}
	
	function tj($controller, $request) {
		$bb = $this->_list_email('339');
		// print_r($bb);
		if (!$bb || count($bb) == 0) {
			return false;
		}
	
		$from = $this->email;
		$subject = '邮箱使用情况';
	
		$aa = array(
			'异常'=>array('w' => " WHERE DATE_FORMAT(FROM_UNIXTIME(timestamp), '%Y-%m-%d') = DATE_FORMAT(DATE_ADD(now(), INTERVAL -1 DAY), '%Y-%m-%d') AND error = 0"),
			'帐号不存在'=>array('w' => " WHERE DATE_FORMAT(FROM_UNIXTIME(timestamp), '%Y-%m-%d') = DATE_FORMAT(DATE_ADD(now(), INTERVAL -1 DAY), '%Y-%m-%d') AND error = 1"),
			'正常'=>array('w' => "  WHERE DATE_FORMAT(FROM_UNIXTIME(timestamp), '%Y-%m-%d') = DATE_FORMAT(DATE_ADD(now(), INTERVAL -1 DAY), '%Y-%m-%d') AND error = 2"),
			'密码错误'=>array('w' => " WHERE DATE_FORMAT(FROM_UNIXTIME(timestamp), '%Y-%m-%d') = DATE_FORMAT(DATE_ADD(now(), INTERVAL -1 DAY), '%Y-%m-%d') AND error = 3"),
			'访问拒绝'=>array('w' => " WHERE DATE_FORMAT(FROM_UNIXTIME(timestamp), '%Y-%m-%d') = DATE_FORMAT(DATE_ADD(now(), INTERVAL -1 DAY), '%Y-%m-%d') AND error = 4"),				
		);
	
		$this->_tj_bb($controller, $aa);
	
		$body = $this->_tj_html($controller, $request, 'log/vlog/tj_list');
		// echo $body;
			
		$this->_tj_mail($from, $bb, $subject, $body);
	
		logdate($controller, $request);
		return true;
	}
	
	function _tj2($v) {
		$this->result_s = 120;
		$this->groupby_s = 'user';
			
		$s = $this->groupby_s;
	
		if (!empty($v['w'])) {
			$fmt = "SELECT concat(%s, '@', domain) AS a, COUNT(*) AS c FROM %s a  %s GROUP BY %s ORDER BY c DESC";
			$sql = sprintf($fmt, $s, $this->tbl, $v['w'], $s);
		} else {
			$fmt = "SELECT concat(%s, '@', domain) AS a, COUNT(*) AS c FROM %s a GROUP BY %s ORDER BY c DESC";
			$sql = sprintf($fmt, $s, $this->tbl, $s);
		}
		echo $sql . "\n";
	
		return $this->_group_tj2($sql);
	}
	
	function _name($row, $px) {
		if (empty($px) || $px == '-' || $px == 'user') {
			$emp = $this->_emp_email($row['a']);
			if ($emp && !empty($emp['name'])) {
				$row['a'] = sprintf("%s [%s]", $emp['name'], $row['a']);
			}
		}
		
		$row = $this->_name_t($row, $px);
		
		return $row;
	}
	
	function _emp_email($email) {
		require_once(dirname(__FILE__) . '/../emp/Emp.class.php');
		$aa = new Emp();
	
		return $aa->_emp_email($email);
	}
	
	function tj3($controller, $request) {
		$bb = $this->_list_email('339');
		// print_r($bb);
		if (!$bb || count($bb) == 0) {
			return false;
		}
		
		$from = $this->email;
		$subject = '邮箱使用情况「IP」';
		
		$aa = array(
			'异常'=>array('w' => " WHERE FROM_UNIXTIME(timestamp) > DATE_ADD(now(), INTERVAL -5 MINUTE) AND error = 0"),
			'帐号不存在'=>array('w' => " WHERE FROM_UNIXTIME(timestamp) > DATE_ADD(now(), INTERVAL -5 MINUTE) AND error = 1"),
			// '正常'=>array('w' => "  WHERE FROM_UNIXTIME(timestamp) > DATE_ADD(now(), INTERVAL -5 MINUTE) AND error = 2"),
			'密码错误'=>array('w' => " WHERE FROM_UNIXTIME(timestamp) > DATE_ADD(now(), INTERVAL -5 MINUTE) AND error = 3"),
			'访问拒绝'=>array('w' => " WHERE FROM_UNIXTIME(timestamp) > DATE_ADD(now(), INTERVAL -5 MINUTE) AND error = 4"),
		);
		
		$data = $this->_tj_bb2($controller, $aa);
		if (count($data) == 0) {
			return false;
		}
		
		$body = $this->_tj_html($controller, $request, 'log/vlog/tj_list3');
		// echo $body;
			
		$this->_tj_mail($from, $bb, $subject, $body);
		
		logdate($controller, $request);
		return true;
	}
	
	function tj2($controller, $request) {
		$bb = $this->_list_email('339');
		// print_r($bb);
		if (!$bb || count($bb) == 0) {
			return false;
		}
	
		$from = $this->email;
		$subject = '邮箱使用情况「IP」';
	
		$aa = array(
			'异常'=>array('w' => " WHERE DATE_FORMAT(FROM_UNIXTIME(timestamp), '%Y-%m-%d') = DATE_FORMAT(DATE_ADD(now(), INTERVAL -1 DAY), '%Y-%m-%d') AND error = 0"),
			'帐号不存在'=>array('w' => " WHERE DATE_FORMAT(FROM_UNIXTIME(timestamp), '%Y-%m-%d') = DATE_FORMAT(DATE_ADD(now(), INTERVAL -1 DAY), '%Y-%m-%d') AND error = 1"),
			'正常'=>array('w' => "  WHERE DATE_FORMAT(FROM_UNIXTIME(timestamp), '%Y-%m-%d') = DATE_FORMAT(DATE_ADD(now(), INTERVAL -1 DAY), '%Y-%m-%d') AND error = 2"),
			'密码错误'=>array('w' => " WHERE DATE_FORMAT(FROM_UNIXTIME(timestamp), '%Y-%m-%d') = DATE_FORMAT(DATE_ADD(now(), INTERVAL -1 DAY), '%Y-%m-%d') AND error = 3"),
			'访问拒绝'=>array('w' => " WHERE DATE_FORMAT(FROM_UNIXTIME(timestamp), '%Y-%m-%d') = DATE_FORMAT(DATE_ADD(now(), INTERVAL -1 DAY), '%Y-%m-%d') AND error = 4"),
		);
	
		$this->_tj_bb2($controller, $aa);
	
		$body = $this->_tj_html($controller, $request, 'log/vlog/tj_list');
		// echo $body;
			
		$this->_tj_mail($from, $bb, $subject, $body);
	
		logdate($controller, $request);
		return true;
	}
	
	function _tj_bb2($controller, $aa) {
		$data = array();
		while (list($k, $v) = each($aa)) {
			$row = $this->_tj3($v);
			if (count($row['data']) == 0) {
				continue;
			}
				
			$row['title'] = $k;
				
			$data[] = $row;
		}
	
		$controller->assign_by_ref('result', $data);
		
		return $data;
	}
	
	function _tj3($v) {
		$this->result_s = 120;
		$this->groupby_s = 'remoteip';
			
		$s = $this->groupby_s;
	
		if (!empty($v['w'])) {
			$fmt = "SELECT %s AS a, user, COUNT(*) AS c FROM %s a  %s GROUP BY %s, user ORDER BY c DESC";
			$sql = sprintf($fmt, $s, $this->tbl, $v['w'], $s);
		} else {
			$fmt = "SELECT %s AS a, user, COUNT(*) AS c FROM %s a GROUP BY %s, user ORDER BY c DESC";
			$sql = sprintf($fmt, $s, $this->tbl, $s);
		}
		echo $sql . "\n";
	
		return $this->_group_tj2($sql);
	}
	
	function _tj_list($a) {
		$db = newdb($this->dsn_r);
		$fmt = "SELECT user, COUNT(user) c FROM %s WHERE %s GROUP BY user ORDER BY c DESC";
		$sql = sprintf($fmt, $this->tbl, $a['w']);
		echo $sql . "\n";
		$result = $db->query($sql);
		$data = array();
		while ($row = $db->fetch_array($result)) {
			$row = $this->_view(null, $row);
	
			$data[] = $row;
		}
	
		$ret['data'] = $data;
	
		return $ret;
	}
	
	function t($controller, $request) {
		require_once(dirname(__FILE__) . '/../info/Qq.class.php');
		$aa = new Qq();
		
		$db = newdb($this->dsn_r);
		$fmt = "SELECT remoteip, logon, COUNT(*) c FROM %s WHERE FROM_UNIXTIME(timestamp) > DATE_ADD(now(), INTERVAL -5 MINUTE) GROUP BY remoteip, logon ORDER BY c DESC";
		// $fmt = "SELECT remoteip, count(*) c FROM %s WHERE FROM_UNIXTIME(timestamp) > DATE_ADD(now(), INTERVAL -1 DAY) GROUP BY remoteip ORDER BY c DESC";
		$sql = sprintf($fmt, $this->tbl);
		echo $sql . "\n";
		$result = $db->query($sql);
		while ($row = $db->fetch_array($result)) {
			// print_r($row);
			
			$user = $row['logon'];
			if ($this->_ignore($user) == 2) {
				echo "S";
				continue;
			}
			
			$ip = $row['remoteip'];
			if ($this->_error($ip)) {
				$aa->_insert($ip, '2', '2');
			}
		}
		
		logdate($controller, $request);
		return true;
	}
	
	function z($controller, $request) {
		require_once(dirname(__FILE__) . '/../info/Qq.class.php');
		$aa = new Qq();
		
		$aa->_insert("119.147.23.17", '2','2');
		
		logdate($controller, $request);
		return true;
	}
	
	function _ignore($user) {
		require_once(dirname(__FILE__) . '/../emp/Emp.class.php');
		$aa = new Emp();
		
		if ($row = $aa->_emp_email($user)) {		
			return $row['vpopmail'];
		}
		
		return false;
	}
	
	function _error($ip) {
		$db = newdb($this->dsn_r);		
		$fmt = "SELECT error, user, COUNT(*) c FROM %s WHERE remoteip = '%s' AND FROM_UNIXTIME(timestamp) > DATE_ADD(now(), INTERVAL -5 MINUTE) GROUP BY error, user ORDER BY c DESC";
		$fmt = "SELECT error, user, COUNT(*) c FROM %s WHERE remoteip = '%s' AND FROM_UNIXTIME(timestamp) > DATE_ADD(now(), INTERVAL -2 HOUR) GROUP BY error, user ORDER BY c DESC";
		$sql = sprintf($fmt, $this->tbl, $ip);
		echo $sql . "\n";
		$result = $db->query($sql);
		
		$ret = array('a'=>0, 'b'=>0, 'c'=>0);
		while ($row = $db->fetch_array($result)) {
			$error = $row['error'];
			$user = $row['user'];
			$c = $row['c'];
			if ($error == '1') {
				$ret['a'] += $c;
			}
			
			if ($error == '2') {
				$ret['b'] += $c;
			}
			
			if ($error == '3') {
				$ret['c'] += $c;
			}			
		}

		$ret['ip'] = $ip;
		print_r($ret);		
		
		$cc = $ret['a'] + $ret['c'] - $ret['b'];
		if ( $cc > 5) {
			echo sprintf("insert... $cc\n", $cc);
			return true;
		}
		
		return false;
	}	
}
?>
